package com.lunwen.dao;

import com.lunwen.domain.Article;
import com.lunwen.utils.JdbcTemplate;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class ArticleDao {

    private JdbcTemplate jdbcTemplate;

    public ArticleDao(){
        jdbcTemplate = JdbcTemplate.getJdbcTemplate();
    }

    //查找所有文章列表
    public List<Article> findAll(){
        String sql = "select * from article";
        try {
            return jdbcTemplate.query(sql,Article.class);
        } catch (SQLException exception) {
            return null;
        }
    }

    //根据aid查学生sid
    public String findSidByBid(int aid){
        String sql = "select sid from article_student_relation where aid=?";
        try {
            List<Map<String, Object>> query = jdbcTemplate.query(sql, aid);
            if (query.size()>0){
                return (String) query.get(0).get("sid");
            }else return null;
        } catch (SQLException exception) {
            exception.printStackTrace();
            return null;
        }
    }

    //按照关键字查找文章
    public List<Article> find(String key){
        String sql = "select * from article where name like \"%\"?\"%\"";
        try {
            return jdbcTemplate.query(sql,Article.class,key);
        } catch (SQLException exception) {
            System.out.println(exception.toString());
            return null;
        }
    }

    //通过aid查找文章内容
    public String findByAid(Integer aid){
        String sql = "select * from article_content where aid=?";
        try {
            return (String) jdbcTemplate.query(sql,aid).get(0).get("content");
        } catch (SQLException exception) {
            return null;
        }
    }

    //新增文章
    public int insert(Article article){
        String sql = "insert into article(name,type) values(?,?)";
        try {
            int result = jdbcTemplate.modify(sql,
                    article.getName(),
                    article.getType()
                    );
            if (result==1){
                String sql1 = "select aid from article where name=? and type=?";
                return jdbcTemplate.query(sql1,
                        Article.class,
                        article.getName(),
                        article.getType()
                        ).get(0).getAid();
            }return -1;
        } catch (SQLException e) {
            return -1;
        }
    }

    //新增文章关联内容
    public int insertContent(Article article){
        String sql = "insert into article_content(aid,content) values(?,?)";
        try {
            return jdbcTemplate.modify(sql,
                    article.getAid(),
                    article.getContent()
            );
        } catch (SQLException e) {
            return -1;
        }
    }

    //新增文章学生关联
    public int insertRelation(int aid, String sid){
        String sql = "insert into article_student_relation values(?,?)";
        try {
            return jdbcTemplate.modify(sql,
                    aid,
                    sid
                    );
        } catch (SQLException exception) {
            return -1;
        }
    }

    //根据aid删除文章
    public int delete(Integer aid){
        String sql1 = "delete from article where aid=?";
        String sql2 = "delete from article_content where aid=?";
        String sql3 = "delete from article_student_relation where aid=?";
        try {
            return
                    (
                            (jdbcTemplate.modify(sql1,aid)==1)&&
                                    (jdbcTemplate.modify(sql2,aid)==1)&&
                                        (jdbcTemplate.modify(sql3,aid)==1)
                    )?1:-1;
        } catch (SQLException e) {
            System.out.println(e.toString());
            return -1;
        }
    }

    //更新文章内容
    public int update(int aid, String content){
        String sql = "update article_content set content=? where aid=?";
        try {
            return jdbcTemplate.modify(sql,
                    content,
                    aid
                    );
        } catch (SQLException e) {
            return -1;
        }
    }

    public static void main(String[] args) {
//        //新增文章
//        Article article = new Article();
//        article.setName("今天天气");
//        article.setType("天气");
//        int aid = insert(article);
//        article.setAid(aid);
//
//        //新增内容
//        String content = "今天天气非常不错！！！";
//        article.setContent(content);
//        insertContent(article);
//
//        //新增学生关联
//        int sid = 1;
//        System.out.println(insertRelation(article.getAid(), sid));

//        System.out.println(delete(5));

//        //模糊查询
//        find("三").forEach(temp->{
//            System.out.println(temp.getName());
//        });

//        //更改文章内容
//        System.out.println(update(1, "bbbbbbbbbbb"));

//        //根据bid查文章内容
//        System.out.println(findByAid(1));
    }


}
